import os
import sys
from typing import Any
import pyodbc
import pandas as pd
import subprocess

class MdbHelper(object):
    """Класс для работы с БД MS Access (.mdb)

    Args:
        object (_type_): _description_
    """
    version = "2.1.0"
    
    def __init__(self,mdb_path:str,autoconnect:bool=False) -> None:
        try:
            self.cur_path = sys._MEIPASS
        except:
            self.cur_path = os.path.dirname(os.path.realpath(__file__))
        self.mdb_sql = f"{self.cur_path}\\mdbt\\mdb-sql.exe"
        self.mdm_query = f"{self.cur_path}\\mdbt\\mdm.sql"
        self.dump_path = f"{self.cur_path}\\mdbt\\dump.csv"
        self.csv_delim = ";"            
        self.mdb_path = mdb_path
        self.connection = None
        drivers = [i for i in pyodbc.drivers() if i.startswith('Microsoft Access Driver')]
        # self.connection_str=f"Driver={{Microsoft Access Driver (*.mdb, *.accdb)}};Dbq={mdb_path};Uid=;Pwd=;"
        try:
            self.connection_str=f"Driver={{{drivers[0]}}};Dbq={mdb_path};Uid=;Pwd=;"
        except: pass
        if autoconnect: 
            self.connection = pyodbc.connect(self.connection_str)
            print("conncected")
        
    def connect(self):
        try:
            self.connection = pyodbc.connect(self.connection_str)
            print("conncected")
        except: raise
    
    def db_info(self)-> Any:
        try:
            return self.connection.getinfo(pyodbc.SQL_DRIVER_NAME)
        except Exception as exp:
            raise exp
        
    # def table_list(self)->list[str]:
    #     try:
    #         sql = "SELECT Name FROM MSysObjects WHERE Type=1 AND Flags=0"
    #         tables = self.connection.execute(sql).fetchall()
    #         table_names = [str(table.Name) for table in tables]
    #         return table_names
    #     except: raise
    
    def get_table_df(self,table:str) ->pd.DataFrame:
        """ Получение датафрейма из MSAccess (mdb) посредством odbc """
        try:
            sql = f"select * from [{table}]"
            return self.get_sql_df(sql)
        except: raise
            
    def get_mdbt_table_df(self) ->pd.DataFrame:
        """ Получение датафрейма из MSAccess (mdb) посредством mdbtools 
            issue http://vsys01775:8282/flea/pyexcelcons3/-/issues/1
            iisue http://vsys01775:8282/flea/pyexcelcons3/-/issues/9
        """
        try:
            dt = subprocess.check_output([self.mdb_sql, "-i", self.mdm_query, "-o", self.dump_path, f"--delim={self.csv_delim}", "-P", self.mdb_path], shell=True,encoding="cp1251")
            df = pd.read_csv(self.dump_path,delimiter=self.csv_delim,header=0,encoding="cp1251",skipfooter=1)
            return df
        except: raise
    
    
    def get_sql_df(self,sql)->pd.DataFrame:
        try:
            df = pd.read_sql(sql,con=self.connection)
            return df
        except: raise
                    
    def close_connection(self,autocommit:bool=True):
        try:
            if autocommit: self.connection.commit()
            self.connection.close()
            print("disconnected")
        except: raise
        